import pymysql

class DBHelper(object):
    DBSHOW_DATABASES = 0
    DBSHOW_TABLES = 1
    DBSHOW_COLUMNS = 2
    DBSHOW_INDEX = 3
    DBSHOW_STATUS = 4

    conn = None

    def __init__(self, dbName=""):
        self.connection_db(dbName)

    def connection_db(self, dbName="mysql", host="localhost", port=3306, user="root", passwd=""):
        '''
        连接数据库
        :param dbName: 数据库名 
        :param host: IP地址
        :param port: 端口号
        :param user: 登录用户
        :param passwd: 登录用户密码
        :return: 
        '''
        try:
            if dbName:
                self.conn = pymysql.connect(host=host, port=port, user=user, passwd=passwd, db=dbName, charset="utf8")
            else :
                self.conn = pymysql.connect(host=host, port=port, user=user, passwd=passwd, charset="utf8")
            if self.conn:
                print("************************ connect DB Status OK")
            else:
                print("************************ connect DB Status No")
        except Exception as e:
            print("connect No error : " + str(e))

    def printSqlInfo(self, msg, sqlPara, args=None, e=None):
        '''
        统一信息格式打印
        :param msg: 操作类型
        :param sqlPara: sql语句
        :param args: sql参数
        :param e: 错误信息
        :return: 
        '''
        print("*********************" + msg)
        print("sql : " + sqlPara)
        if e:
            errorStr = str(e)
            print("error :" + errorStr)
        if(not (args)):
            if (args.__class__ == list.__class__):
                for arg in args:
                    print("[" + str(arg) + "]")
            else:
                print("[" + str(args) + "]")
        print("*********************")

    def db_execute(self, sqlparam):
        try:
            self.conn.connect()
            cursor = self.conn.cursor()
            cursor.execute(sqlparam)
            # 提交，不然无法保存新建或者修改的数据
            self.conn.commit()
            # 关闭游标
            cursor.close()
            # 关闭连接
            self.conn.close()
            printSqlInfo("执行sql语句成功", sql)
        except Exception as e:
            printSqlInfo("执行sql语句失败", sql, e)

    def db_creatDataBase(self, dataBaseName):
        sql = "create database " + dataBaseName
        try:
            self.conn.connect()
            cursor = self.conn.cursor()
            cursor.execute(sql)
            # 提交，不然无法保存新建或者修改的数据
            self.conn.commit()
            # 关闭游标
            cursor.close()
            # 关闭连接
            self.conn.close()
            printSqlInfo("创建数据库成功", sql)
        except Exception as e:
            printSqlInfo("创建数据库失败", sql, e)

    def db_deleteDataBase(self, dataBaseName):
        sql = "drop database " + dataBaseName
        try:
            self.conn.connect()
            cursor = self.conn.cursor()
            cursor.execute(sql)
            # 提交，不然无法保存新建或者修改的数据
            self.conn.commit()
            # 关闭游标
            cursor.close()
            # 关闭连接
            self.conn.close()
            printSqlInfo("删除数据库成功", sql)
        except Exception as e:
            printSqlInfo("删除数据库失败", sql, e)

    def db_show(self, param, dataName=None, tableName=None):
        sql = "show "
        if param == 0:  # 列出 MySQL 数据库管理系统的数据库列表
            sql += "databases"
        elif param == 1 and dataName: # 显示指定数据库的所有表，使用该命令前需要使用 use 命令来选择要操作的数据库
            sql += "tables"
        elif param == 2 and tableName:  # 显示数据表的属性，属性类型，主键信息 ，是否为 NULL，默认值等其他信息
                sql += "columns from " + tableName
        elif param == 3 and tableName:  # 显示数据表的详细索引信息，包括PRIMARY KEY（主键）
                sql += "index from " + tableName
        elif param == 4 and dataName:  # 该命令将输出Mysql数据库管理系统的性能及统计信息
            '''
            SHOW TABLE STATUS LIKE [FROM db_name] [LIKE 'pattern'] \G:
            mysql> SHOW TABLE STATUS  FROM RUNOOB;   # 显示数据库 RUNOOB 中所有表的信息
            mysql> SHOW TABLE STATUS from RUNOOB LIKE 'runoob%';     # 表名以runoob开头的表的信息
            mysql> SHOW TABLE STATUS from RUNOOB LIKE 'runoob%'\G;   # 加上 \G，查询结果按列打印
            '''
            sql += "status like from " + dataName
        else:
            sql = ""
        try:
            self.conn.connect()
            cursor = self.conn.cursor()
            if param == 1 and dataName:
                cursor.execute("use " + dataName)
            cursor.execute(sql)
            print(cursor.fetchall())
            # 提交，不然无法保存新建或者修改的数据
            self.conn.commit()
            # 关闭游标
            cursor.close()
            # 关闭连接
            self.conn.close()
            self.printSqlInfo("基础操作成功", sql)
        except Exception as e:
            self.printSqlInfo("基础操作失败", sql, e)

    def db_creatTable(self, tbaleName, args):
        sql = "create table " + tbaleName
        try:
            self.conn.connect()
            cursor = self.conn.cursor()
            if args:
                sql += args
            # 解决中文乱码
            sql += "ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;"
            cursor.execute(sql)
            # 提交，不然无法保存新建或者修改的数据
            self.conn.commit()
            # 关闭游标
            cursor.close()
            # 关闭连接
            self.conn.close()
            self.printSqlInfo("表格创建成功", sql)
        except Exception as e:
            self.printSqlInfo("表格创建失败", sql, e)

    def db_deleteTable(self, dbTable):
        sql = "delete table " + dbTable
        try :
            self.conn.connect()
            cursor = self.conn.cursor()
            cursor.execute(sql)
            # 提交，不然无法保存新建或者修改的数据
            self.conn.commit()
            # 关闭游标
            cursor.close()
            # 关闭连接
            self.conn.close()
            self.printSqlInfo("删除表格" + dbTable + "成功", sql, args)
        except Exception as e:
            self.printSqlInfo("删除表格" + dbTable + "失败", sql, args, e)

    def db_select(self, dbTable, where="where par0=%s,par1=%s", args=None):
        sql = "select * from " + dbTable
        try:
            self.conn.connect()
            cursor = self.conn.cursor()
            if args:
                sql += " " + where
                cursor.execute(sql, args)
            else:
                cursor.execute(sql)
            selects = cursor.fetchall()
            # 提交，不然无法保存新建或者修改的数据
            self.conn.commit()
            # 关闭游标
            cursor.close()
            # 关闭连接
            self.conn.close()
            self.printSqlInfo("查询成功", sql, args)
            return selects
        except Exception as e:
            self.printSqlInfo("查询失败", sql, args, e)
            return None

    def db_update(self, dbTable, setParams, where="", args=None):
        sql = "update " + dbTable + " set " + setParams
        try:
            '''
            cursor.execute("update tb7 set para1='123', para2='123' where id=%s", (11))
            '''
            self.conn.connect()
            cursor = self.conn.cursor()
            if args:
                sql += " where " + where
                cursor.execute(sql, args)
            else:
                cursor.execute(sql)
            # 提交，不然无法保存新建或者修改的数据
            self.conn.commit()
            # 关闭游标
            cursor.close()
            # 关闭连接
            self.conn.close()
            self.printSqlInfo("更新成功", sql, args)
        except Exception as e:
            self.printSqlInfo("更新失败", sql, args, e)
            return None

    def db_insert(self, dbTable, sqlParam, args):
        sql = "insert into " + dbTable + str(sqlParam)
        try:
            '''
            sqlParam : (user,pass,licnese)values(%s,%s,%s)
            cursor.execute("insert into tb(user,pass,licnese)values(%s,%s,%s)", ("u1","u1pass","11111"))
            cursor.executemany("insert into tb(user,pass,licnese)values(%s,%s,%s)", [("u1","u1pass","11111"),("u2","u2pass","22222")])
            '''
            self.conn.connect()
            cursor = self.conn.cursor()
            if (args.__class__ == list.__class__):
                cursor.executemany(sql, args)
            else:
                cursor.execute(sql, args)
            # 提交，不然无法保存新建或者修改的数据
            self.conn.commit()
            # 关闭游标
            cursor.close()
            # 关闭连接
            self.conn.close()
            self.printSqlInfo("保存成功", sql, args)
            return True
        except Exception as e:
            self.printSqlInfo("保存失败", sql, args, e)
            return False

    def db_insert_norepeat(self, dbTable, sqlParam, selectParam, dualselectParamName, dualwhereParam=" WHERE par0='00'"):
        '''
        不重复插入数据
        :param dbTable: 
        :param sqlParam: 
        :param selectParam: 
        :param dualselectParamName: 
        :param dualwhereParam: 
        :return: 
        '''
        sql = "insert into " + dbTable + "(" + sqlParam + ") SELECT " + selectParam + \
              " FROM DUAL WHERE NOT EXISTS(SELECT " + dualselectParamName + " FROM " + dbTable + dualwhereParam + ")"
        try:
            '''
            # INSERT INTO card(cardno, cardnum) SELECT '111', '100' FROM DUAL WHERE NOT EXISTS(SELECT cardno FROM card WHERE cardno = '111');
            '''
            print(sql)
            self.conn.connect()
            cursor = self.conn.cursor()
            cursor.execute(sql)
            # 提交，不然无法保存新建或者修改的数据
            self.conn.commit()
            # 关闭游标
            cursor.close()
            # 关闭连接
            self.conn.close()
            self.printSqlInfo("保存成功", sql)
            return True
        except Exception as e:
            if(False):
                self.printSqlInfo("保存失败-数据已存在", sql, e)
            else:
                self.printSqlInfo("保存失败", sql, e)
            return False

    def db_delete(self, dbTable, where="where par0=%s,par1=%s", args=None):
        sql = "delete from " + dbTable
        try :
            # DELETE FROM runoob_tbl WHERE runoob_id=3;
            self.conn.connect()
            cursor = self.conn.cursor()
            if args:
                sql += " " + where
                cursor.execute(sql, args)
            else:
                cursor.execute(sql)
            # 提交，不然无法保存新建或者修改的数据
            self.conn.commit()
            # 关闭游标
            cursor.close()
            # 关闭连接
            self.conn.close()
            self.printSqlInfo("删除成功", sql, args)
        except Exception as e:
            self.printSqlInfo("删除失败", sql, args, e)